package com.hyper_yang._01_jdbc.dml;

import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;

import java.sql.*;

public class DMLTest {
    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        // 1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test1", "root", "abc123");
        // 3.准备 sql语句
//        String sql = "INSERT INTO t_account VALUES(0,'sakiko',1000);";
        String sql = "INSERT INTO t_account VALUES(?,?,?);";
        // 4.创建 sql发送器
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1, 0);
        preparedStatement.setObject(2, "sakiko");
        preparedStatement.setObject(3, 1000);
        // 5.发送 sql,获取结果(受影响的行)
        int i = preparedStatement.executeUpdate();
        // 6.输出结果
        System.out.println(i > 0 ? "添加成功" : "添加失败");
        // 7.释放资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testDelete() throws SQLException {
        // 注册
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test1", "root", "abc123");
        // sql
        String sql = "DELETE FROM t_account WHERE `name` = 'sakiko';";
        // 获取发射器
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 执行 (返回的是受影响的行)
        int i = preparedStatement.executeUpdate();
        System.out.println(i>0?"删除成功":"删除失败");
        // 释放资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test1", "root", "abc123");
        // 准备sql语句
        String sql = "UPDATE t_account SET balance = 20000 WHERE `name` = 'sakiko';";
        // 获取发射器
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 发射 sql语句，返回结果
        int i = preparedStatement.executeUpdate();
        // 输出结果
        System.out.println(i > 0 ? "更新成功" : "更新失败");
        // 释放资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testQuery() throws ClassNotFoundException, SQLException {
        // 注册驱动
        // 连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test1", "root", "abc123");
        // 准备sql语句
        String sql = "select * from t_account";
        // 获取发射器
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 执行查询操作
        ResultSet resultSet = preparedStatement.executeQuery();
        // 验证输出结果
        Assertions.assertTrue(resultSet.next(), "ResultSet is empty!");
        // 打印查询结果
        System.out.println("Query Results:");
        do {
            // 假设 t_account 表有两列：id (int) 和 name (String)
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int balance = resultSet.getInt("balance");
            System.out.println("ID: " + id + ", Name: " + name + ", Balance: " + balance);
        } while (resultSet.next());
        // 释放资源
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void testUpdate2() throws ClassNotFoundException, SQLException {
        // 注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///test1", "root", "abc123");
        // 准备sql语句
        String sql = "UPDATE t_account SET balance = ? WHERE `name` = ?;";
        // 获取发射器
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 给 ?占位符赋值
        preparedStatement.setObject(1, 10000);
        preparedStatement.setObject(2,"sakiko");
        // 发射 sql语句，返回结果
        int i = preparedStatement.executeUpdate();
        // 输出结果
        System.out.println(i > 0 ? "更新成功" : "更新失败");
        // 释放资源
        preparedStatement.close();
        connection.close();
    }
}
